Updating data when there are no fields in the table

You can perform an update in iLoader when the update file doesn't contain all the data that is in the table to be updated.

When iLoader does an update, it finds the fields to update based on the names of the fields as specified in the TABLE_DEFINITION section of the iLoader script. This means that it is possible to take an update file that contains only 2 fields and use this file to update a table that contains many more fields.

Procedure

To set up the update, do the following:

1) Ensure that the update file contains a key that can be used to link to the loaded data.

…

9772847,Mrs

9723447,Prof.

1232720,Mr

…

2) In the control, or master file, create the Table Declaration.

a. Specify the format of the Update file- delimited or fixed

b. Specify the delimiter (omit if not required)

c. Specify the script file containing the table definition

d. Specify the name of the update file

e. Specify the TYPE as UPDATE or UPDATE_ONLY.

f. Specify the WIDTH to match the number of records in the update file.

g. Specify the load date (if omitted, will default to today)

;Customer TABLE

BEGIN TABLE_DECLARATION

format = delimited

delimiter = COMMA

scriptfile = Customer_Update_Script.txt

datfile = Customer_Update_Data.dat

type = update

width = 2

END TABLE_DECLARATION

3) In the Update Script file:

a. Specify the table to be updated:

TABLE = [DEMO].[CUSTOMER]

b. Specify the link or update key

KEY = CUSTOMER_ID

c. Create the Table Definition. Make sure that the Field names in the Table Definition are a match for the fields in the table to be updated and that the ordinal position of the fields matches the data in the update file.

The Link Key must also be part of the Table_Definition.

BEGIN TABLE_DEFINITION

CUSTOMER_ID,INTEGER,0,10

TITLE, TEXT, 1, 10

END TABLE_DEFINITION

4) The script is now ready to run. In the examples above, the CUSTOMER table will be updated using CUSTOMER_ID as a key. For the records in the update file, the TITLE field will be updated to match the data in the update file.

• Appendix
  • Default Environment Variables
  • Sample Database Loads
  • Array Fields
  • Error Messages
  • Common Issues
  • Data Issues and Checklist
  • Updating Data When There Are No Fields In The Table
  • Updating Non-null Fields Only